﻿CREATE TABLE [dbo].[ApptOrder](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OrderType] NVARCHAR(50) NULL,
	[BkMemberId] BIGINT NULL,
	[WxUserId] BIGINT NULL,
	[CardNo] NVARCHAR(50) NULL,
	[CardName] NVARCHAR(50) NULL,
	[DeptId] NVARCHAR(50) NULL,
	[DeptName] NVARCHAR(50) NULL,
	[DoctorId] NVARCHAR(50) NULL,
	[DoctorName] NVARCHAR(50) NULL,
	[ScheduleId] NVARCHAR(50) NULL,
	[SubjectId] NVARCHAR(50) NULL,
	[SbConfirmed] NVARCHAR(50) NULL,
	[OrderNo] NVARCHAR(50) NOT NULL,
	[RegDate] NVARCHAR(50) NULL,
	[RegNo] NVARCHAR(50) NULL,
	[SuggestBeginTime] NVARCHAR(50) NULL, 
    [SuggestEndTime] NVARCHAR(50) NULL, 
    [Location] NVARCHAR(200) NULL,	
	[FlowNo] NVARCHAR(50) NULL,
	[WaitNo] NVARCHAR(50) NULL,
	[WaitCount] NVARCHAR(50) NULL,
	[PatType] NVARCHAR(50) NULL,
	[OrderStatus] INT NOT NULL,
	[TimeSlice] NVARCHAR(50) NULL,
	[StartTime] NVARCHAR(50) NULL,
	[EndTime] NVARCHAR(50) NULL,
	[RegType] NVARCHAR(50) NULL,
	[PayState] NVARCHAR(50) NULL, 
	[RefundReason] NVARCHAR(50) NULL, 
    [HaltState] NVARCHAR(50) NULL DEFAULT 0,
	[Fee] DECIMAL(18, 2) NULL,
	[TreatFee] DECIMAL(18, 2) NULL,
	[OtherFee] DECIMAL(18, 2) NULL,
	[SumFee] DECIMAL(18, 2) NULL,
	[CreateTime] DATETIME NULL,
	[LockTime] DATETIME NULL,
	[ApptTime] DATETIME NULL,
	[PayTime] DATETIME NULL,
	[FetchTime] DATETIME NULL,
	[RefundTime] DATETIME NULL,
	[CancelByUserTime] DATETIME NULL,
	[CancelByHospTime] DATETIME NULL,	
	[CancelBySysTime] DATETIME NULL, 
	[NextCheckTime] DATETIME NULL,
	[OrderStatusOnSetCheck] INT NULL, 
	[CheckReserved] NVARCHAR(50) NULL , 
	[Remark] NTEXT NULL,        
    [WxTransactionId] NVARCHAR(50) NULL, 
    [WxRefundId] NVARCHAR(50) NULL, 
	[VisitNo] NVARCHAR(50) NULL,     
    [Sqno] NVARCHAR(50) NULL, 	
    CONSTRAINT [PK_ApptOrder] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单类型 0-微信平台预约订单  1-其它平台预约订单 2-微信平台挂号订单' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'OrderType'
GO


GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'医生院内编码,医生代码为空时为挂到科室' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'DoctorId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'医生名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'DoctorName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'OrderNo'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0未锁号 1锁号中 2锁号失败 3已锁号 4取消锁号中 5取消锁号成功 6取消锁号失败 7预约中 8预约成功 9预约失败 10取消预约中 11取消预约成功 12取消预约失败 13支付中 14支付失败 15支付成功 16取号中 17取号失败 18退款中 19退款成功 20退款失败 21取号成功' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'OrderStatus'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder', @level2type=N'COLUMN',@level2name=N'Remark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'挂号预约记录' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ApptOrder'
GO



EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'BkMember.Id',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'BkMemberId'
GO

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'患者信息卡卡号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CardNo'
GO

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'科室院内编码',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'DeptId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'科室名称',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'DeptName'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'就诊日期',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RegDate'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，号序',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RegNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'预约总时段：上午，下午，夜间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'TimeSlice'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分时开始时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'StartTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分时结束时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'EndTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'号别',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RegType'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'支付状态:0-未付款,1-已付款,2-退款中,3-已退款',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'PayState'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'挂号费',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'Fee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'诊疗费',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'TreatFee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'其他费用',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'OtherFee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'总费用',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SumFee'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'订单创建时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CreateTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'锁号成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'LockTime'
GO

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'用户取消预约的时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CancelByUserTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'医院取消订单时间（停诊）',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'CancelByHospTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'支付成功时间，支付成功后微信返回',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'PayTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'退款成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RefundTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'系统下次检查时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'NextCheckTime'
GO

GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'预约成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'ApptTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'FetchTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，建议就诊开始时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SuggestBeginTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，建议就诊结束时间',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SuggestEndTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，就诊地点',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'Location'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'停诊状态：0-正常，1-已停诊',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'HaltState'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'系统取消预约的时间(超时或系统出错而导致的取消)',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CancelBySysTime'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'系统检查预留字段',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = 'CheckReserved'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'退款原因，1-取号失败而退费，2-支付完成时订单已关闭（支付超时）',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'RefundReason'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'设置系统下次检查时间时的订单状态',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'OrderStatusOnSetCheck'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'微信支付交易号，支付成功后微信返回',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WxTransactionId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'微信支付退款单号，退款成功后微信返回',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WxRefundId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，就诊流水号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'VisitNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'分时序号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'Sqno'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'WxUser.Id',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WxUserId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，挂号流水号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'FlowNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，挂号排队号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WaitNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，等待人数',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'WaitCount'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'取号成功后，费用类别',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'PatType'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'是否已社保登记, 1－已登记，0－未登记',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SbConfirmed'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'排班序号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'ScheduleId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'科目唯一代码',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SubjectId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'卡主姓名',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CardName'